-- drop table [dbo].[XH_GameUser]
CREATE TABLE [dbo].[XH_GameUser]( -- 代理关系表，与QPGameUserDB.AccountsInfo中的记录是一一对应的。添加运营号，或者运营号设置下级时往这张表插入记录
	[id] [int] IDENTITY(1,1) NOT NULL,
	[userId] [int] NOT NULL, -- 玩家或代理的user id，游戏方用户ID
	[userName] [nvarchar](200) NULL,-- 用户名或邀请码,须唯一,暂时没用，
	[utype] [int] NOT NULL, -- 玩家类型：0普通玩家1代理玩家
	[parentId] [int]  NULL, -- 父ID，父ID为空意味着这是第一层代理
	[topId] [int] NOT NULL, -- 顶级id，关联adminUser表，也就是运营号ID，
	[crtOpr] [int] NULL, -- 操作人，这里关联QPTreasureDB.AdminUser表id
	[crtDate] [datetime] NULL, -- 添加时间
 CONSTRAINT [PK_XH_GameUser_id] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 -- drop table [dbo].[XH_GoldDetail]
CREATE TABLE [dbo].[XH_GoldDetail]( -- 金币明细
	[id] [int] IDENTITY(1,1) NOT NULL,
	[userId] [int] NOT NULL, -- 当userType=0或2时，关联的是AccountsInfo.UserId，当userType=2时，关联的是运营号AdminUser.id
	[amt] [int] NOT NULL, -- 本次充值、消耗、提成等操作 的金币个数，可正可负
	[balance] [int] NOT NULL, -- 交易后金币余额
	[busiType] [int] NOT NULL, -- 类型：1=充值或扣值、2=提成
	-- 新增
	[userType] [int] NOT NULL, -- 类型：0=普通玩家、1=代理玩家、2=运营号
	[pId] [int], -- 金币消耗导致提成，而金币消耗记录定时统计，这里关联那笔统计的记录（暂定）
	[remark] [nvarchar](200), -- 备注
	[crtOpr] [int] NOT NULL, -- 操作人这里关联QPTreasureDB.AdminUser表id
	[crtDate] [datetime] NOT NULL, -- 操作时间
	[dateID] [int] , -- 提成时所用到的 数据ID  对应  GameRevenueInfo里的dateID
	[sourceUserId] [int] , -- 提成时的来源用户ID  对应  GameRevenueInfo里的userId
	[kindID] [int] , -- 提成时所用到的 数据游戏ID  对应  GameRevenueInfo里的kindID 统计口径与原表 保持一致
 CONSTRAINT [PK_XH_GoldDetail_id] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


 -- drop table [dbo].[XH_GoldDetail]
CREATE TABLE [dbo].[XH_Rebate]( -- 提成表
	[id] [int] IDENTITY(1,1) NOT NULL,
	[dateId] [int] NOT NULL, -- 提成时所用到的 数据ID  对应  GameRevenueInfo里的dateID
	[CollectDate] [datetime] NOT NULL, -- dateId对应的时间
	[Score] [int] NOT NULL, -- 本次税收的总额
	[rebateAmt] [int] NOT NULL, -- 本次代理提成的总额
	[topRebateAmt] [int] NOT NULL, -- 本次运营号提成的总额
	[userCount] [int] NOT NULL, -- 本次计算提成涉及到代理数量
	[topUserCount] [int] NOT NULL, -- 本次计算提成涉及到运营号数量
	[detailCount] [int] NOT NULL, -- 本次计算提成涉及到金币明细数量
	[automaticTrigger] [int], -- 本次计算提成是不是job跑的 0否 1是
	[remark] [nvarchar](200), -- 备注
	[crtOpr] [int] , -- 操作人这里关联QPTreasureDB.AdminUser表id
	[crtDate] [datetime] NOT NULL, -- 操作时间
 CONSTRAINT [PK_XH_Rebate_id] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[XH_Rebate_Balance]( -- 提成导致余额变动表
	[id] [int] IDENTITY(1,1) NOT NULL,
	[pId] [int], -- XH_Rebate表ID
	[userType] [int] NOT NULL, -- 类型：0=普通玩家、1=代理玩家、2=运营号
	[userId] [int] NOT NULL, -- 当userType=0或2时，关联的是AccountsInfo.UserId，当userType=2时，关联的是运营号AdminUser.id
	[rebateScore] [int] NOT NULL, -- 获得的提成	
	[remark] [nvarchar](200), -- 备注
	[crtOpr] [int], -- 操作人这里关联QPTreasureDB.AdminUser表id
	[crtDate] [datetime] NOT NULL, -- 操作时间
 CONSTRAINT [PK_XH_Rebate_Balance_id] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE VIEW [dbo].[View_GoldDetail] AS 
select
	busiType as  busiType,
	userId as userId,		
	amt as amt,
	crtDate as crtDate,
	'充值' as gameName	
	from QPTreasureDB.dbo.XH_GoldDetail  
union all			
select 
	3 as busiType,
	rul.userId as userId,
	rul.Score as amt,
	rul.LeaveTime as crtDate,
	kind.KindName as gameName
	from  QPTreasureDB.dbo.RecordUserLeave rul left join  QPServerInfoDB.dbo.GameKindItem kind on rul.KindID=kind.KindID
union all			
select 
	4 as busiType,
	ggsi.userId as userId,
	ggsi.GiveScore as amt,
	ggsi.CollectDate as crtDate,
	kind.KindName as gameName
	from  QPTreasureDB.dbo.GameGiveScoreInfo ggsi left join  QPServerInfoDB.dbo.GameKindItem kind on ggsi.KindID=kind.KindID
 